package com.example.backend.repository;

import com.example.backend.domain.entity.User;
import com.example.backend.domain.enums.Role;
import com.example.backend.domain.enums.UserStatus;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.time.Instant;
import java.util.List;
import java.util.Optional;

public interface UserRepository extends JpaRepository<User, Long>, JpaSpecificationExecutor<User> {
    
    // 基础查询
    Optional<User> findByUsername(String username);
    Optional<User> findByEmail(String email);
    boolean existsByUsername(String username);
    boolean existsByEmail(String email);
    
    // 分页查询
    Page<User> findByRole(Role role, Pageable pageable);
    Page<User> findByStatus(UserStatus status, Pageable pageable);
    Page<User> findByDepartment(String department, Pageable pageable);
    Page<User> findByRoleAndStatus(Role role, UserStatus status, Pageable pageable);
    
    // 搜索查询
    @Query("SELECT u FROM User u WHERE " +
           "(:keyword IS NULL OR " +
           "u.username LIKE %:keyword% OR " +
           "u.fullName LIKE %:keyword% OR " +
           "u.email LIKE %:keyword%) " +
           "AND (:role IS NULL OR u.role = :role) " +
           "AND (:status IS NULL OR u.status = :status) " +
           "AND (:department IS NULL OR u.department = :department)")
    Page<User> searchUsers(@Param("keyword") String keyword,
                          @Param("role") Role role,
                          @Param("status") UserStatus status,
                          @Param("department") String department,
                          Pageable pageable);
    
    // 统计方法
    long countByRole(Role role);
    long countByStatus(UserStatus status);
    long countByDepartment(String department);
    long countByLastLoginAtAfter(Instant instant);
    long countByCreatedAtAfter(Instant instant);
    long countByCreatedAtBetween(Instant start, Instant end);
    
    // 获取最新用户
    List<User> findTop5ByOrderByCreatedAtDesc();
    List<User> findTop10ByOrderByLastLoginAtDesc();
    
    // 获取活跃用户
    @Query("SELECT u FROM User u WHERE u.lastLoginAt >= :since ORDER BY u.lastLoginAt DESC")
    List<User> findActiveUsers(@Param("since") Instant since);
    
    // 获取部门用户统计
    @Query("SELECT u.department, COUNT(u) FROM User u WHERE u.department IS NOT NULL GROUP BY u.department")
    List<Object[]> getDepartmentStats();
    
    // 获取角色分布统计
    @Query("SELECT u.role, COUNT(u) FROM User u GROUP BY u.role")
    List<Object[]> getRoleDistribution();
    
    // 获取用户趋势数据
    @Query("SELECT DATE(u.createdAt) as date, COUNT(u) as count FROM User u " +
           "WHERE u.createdAt >= :startDate AND u.createdAt <= :endDate " +
           "GROUP BY DATE(u.createdAt) ORDER BY date ASC")
    List<Object[]> getUserTrendData(@Param("startDate") Instant startDate, @Param("endDate") Instant endDate);
    
    // 获取登录统计
    @Query("SELECT COUNT(u) FROM User u WHERE u.loginCount >= :minCount")
    long countByLoginCountGreaterThanEqual(@Param("minCount") Integer minCount);
    
    @Query("SELECT COUNT(u) FROM User u WHERE u.loginCount BETWEEN :minCount AND :maxCount")
    long countByLoginCountBetween(@Param("minCount") Integer minCount, @Param("maxCount") Integer maxCount);
}


